function doGet() {
  const data = {
    lastUpdated: new Date().toLocaleString(),
    inProgressProjects: getInProgressProjects(),
    projectStats: getProjectStats(),
    announcements: getAnnouncements(),
    calendar: getCalendarData()
  };
  
  return ContentService.createTextOutput(JSON.stringify(data))
    .setMimeType(ContentService.MimeType.JSON);
}

/**
 * PAGE 1: Unique In-Progress Projects
 */
function getInProgressProjects() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  const seenProjects = new Set();
  const results = [];

  sheets.forEach(sheet => {
    if (sheet.getName() === "Announcements") return;
    const rows = sheet.getDataRange().getValues();
    const dataRows = rows.slice(1);

    dataRows.forEach(row => {
      const name = row[2];
      const isComplete = row[6] !== "";
      // Filter: In Progress AND Unique Name
      if (!isComplete && name && !seenProjects.has(name)) {
        seenProjects.add(name);
        results.push({
          type: row[1],
          name: name,
          owner: row[4],
          due: formatDate(row[5])
        });
      }
    });
  });
  return results;
}

/**
 * PAGE 2: Status Aggregation (Due, Completed, Overdue)
 */
function getProjectStats() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  const stats = { completed: [], overdue: [], dueSoon: [] };
  const today = new Date();

  sheets.forEach(sheet => {
    if (sheet.getName() === "Announcements") return;
    const rows = sheet.getDataRange().getValues().slice(1);

    rows.forEach(row => {
      const dueDate = new Date(row[5]);
      const completionDate = row[6];
      const project = { name: row[2], owner: row[4], date: formatDate(row[5]) };

      if (completionDate !== "") {
        stats.completed.push(project);
      } else if (dueDate < today) {
        stats.overdue.push(project);
      } else {
        stats.dueSoon.push(project);
      }
    });
  });
  return stats;
}

/**
 * PAGE 3: Announcements
 * Sheet structure: [Date, Title, Content]
 */
function getAnnouncements() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const allSheets = ss.getSheets();
  let sheet = null;

  // This loop looks through every tab to find the right one
  for (let i = 0; i < allSheets.length; i++) {
    let name = allSheets[i].getName().toLowerCase().trim(); // Convert to lowercase and remove spaces
    if (name === "announcements" || name === "announcement") {
      sheet = allSheets[i];
      break;
    }
  }

  // If even the "smart search" fails
  if (!sheet) {
    return [{ title: "ERROR", content: "Could not find a tab named 'Announcements' in your Google Sheet." }];
  }

  const rows = sheet.getDataRange().getValues();
  if (rows.length < 2) return [];

  return rows.slice(1).map(r => ({
    date: r[0] ? formatDate(r[0]) : "",
    title: r[1] || "No Title",
    content: r[2] || "No Content"
  }));
}

/**
 * PAGE 4: Google Calendar (Events & Birthdays)
 */
function getCalendarData() {
  const now = new Date();
  const startOfMonth = new Date(now.getFullYear(), now.getMonth(), 1);
  const endOfMonth = new Date(now.getFullYear(), now.getMonth() + 1, 0);
  
  // Gets events from your primary calendar
  const events = CalendarApp.getDefaultCalendar().getEvents(startOfMonth, endOfMonth);
  
  return events.map(e => ({
    title: e.getTitle(),
    start: Utilities.formatDate(e.getStartTime(), Session.getScriptTimeZone(), "MMM dd"),
    isBirthday: e.getTitle().toLowerCase().includes("birthday")
  }));
}

function formatDate(date) {
  return (date instanceof Date) ? Utilities.formatDate(date, Session.getScriptTimeZone(), "MMM dd") : date;
}